﻿Imports System.Data
Imports System.Data.SqlClient
Imports Entidad
Public Class ArticuloDatos

    Function obtenerArticulo(codArticulo As String) As Articulo
        Dim articulo As Articulo = Nothing
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT asu.ART_Codigo, a.ART_Descripcion, a.FAM_Codigo, a.UNM_Codigo, " &
                                  "um.UNM_Abreviatura, a.ART_Percepcion, a.ART_ZEmergencia, asu.ARS_CantidadDesp, " &
                                  "ISNULL(aa.ARA_Stock,0) AS ARA_Stock, ap.PROV_Codigo, p.PROV_DesCorta, a.ART_Activo " &
                                  "FROM ArticuloSucursal asu " &
                                  "LEFT JOIN Articulo a " &
                                  "ON a.ART_Codigo = asu.ART_Codigo AND a.ART_Activo = '1' " &
                                  "LEFT JOIN ArticuloAlmacen aa " &
                                  "ON aa.ART_Codigo = a.ART_Codigo AND aa.ARA_Activo = '1' " &
                                  "INNER JOIN UnidadMedida um " &
                                  "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " &
                                  "LEFT JOIN ArticuloProveedor ap " &
                                  "ON ap.ART_Codigo = asu.ART_Codigo AND ap.ARP_Activo = '1' " &
                                  "LEFT JOIN Proveedor p " &
                                  "ON p.PROV_Codigo = ap.PROV_Codigo " &
                                  "WHERE asu.ART_Codigo = '" & codArticulo & "' AND asu.ARS_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            Dim ds As New DataSet
            adapter.Fill(ds)

            articulo = New Articulo("-1", "", "", "", "", "", "", CInt("0"), CInt("0"), "", "", "")

            If ds.Tables(0).Rows.Count > 0 Then
                articulo = New Articulo(ds.Tables(0).Rows(0)("ART_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("ART_Descripcion").ToString.Trim, ds.Tables(0).Rows(0)("FAM_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("UNM_Codigo").ToString.Trim,
                                        ds.Tables(0).Rows(0)("ART_Percepcion").ToString.Trim, ds.Tables(0).Rows(0)("ART_ZEmergencia").ToString.Trim, ds.Tables(0).Rows(0)("UNM_Abreviatura").ToString.Trim, CInt(ds.Tables(0).Rows(0)("ARS_CantidadDesp").ToString.Trim),
                                        CInt(ds.Tables(0).Rows(0)("ARA_Stock").ToString.Trim), ds.Tables(0).Rows(0)("PROV_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("PROV_DesCorta").ToString.Trim, ds.Tables(0).Rows(0)("ART_Activo").ToString.Trim)
            End If
        Catch e As Exception
            articulo = Nothing
        Finally
            coneccion.Close()
        End Try

        Return articulo
    End Function

    Function obtenerArticulosXFiltro(codArticulo As String, descrip As String, codProveedor As String, codAlmacen As String, codFamilia As String, aPartirde As Integer, dscto As Decimal, tipo As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Dim query As String = ""
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            If tipo = "1" Then
                query = "SELECT a.ART_Codigo,LTRIM(RTRIM(a.ART_Descripcion)) AS Articulo,um.UNM_Abreviatura,LTRIM(RTRIM(p.PROV_DesCorta)) AS Descripcion,aa.ARA_Stock, " &
                        "a.ART_PrecioSoles, a.ART_PrecioDolares, " &
                        "a.ART_Percepcion, a.ART_ZEmergencia, ar.ARS_CantidadDesp " &
                        "FROM ArticuloAlmacen aa " &
                        "INNER JOIN Articulo a " &
                        "ON a.ART_Codigo = aa.ART_Codigo AND a.ART_Activo = '1' " &
                        "INNER JOIN UnidadMedida um " &
                        "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " &
                        "INNER JOIN ArticuloProveedor ap " &
                        "ON ap.ART_Codigo = a.ART_Codigo AND ap.ARP_Activo = '1' " &
                        "INNER JOIN Proveedor p " &
                        "ON p.PROV_Codigo = ap.PROV_Codigo " &
                        "INNER JOIN ArticuloSucursal ar " &
                        "ON ar.ART_Codigo = aa.ART_Codigo AND ar.ARS_Activo = '1' " &
                        "WHERE aa.ARA_Activo = '1' AND LEN(aa.ART_Codigo) > 6 " &
                        "AND aa.ALM_Codigo = '" & codAlmacen & "' AND aa.ARA_Stock > 0"

            ElseIf tipo = "2" Then
                query = "SELECT aa.ART_Codigo, a.ART_Descripcion, um.UNM_Abreviatura, p.PROV_DesCorta, " &
                        "ISNULL(d.CantMin1,0) AS CantMin1, ISNULL(d.Dscto1,0) AS Dscto1, ISNULL(d.CantMin2,0) AS CantMin2, ISNULL(d.Dscto2,0) AS Dscto2 " &
                        "FROM ArticuloAlmacen aa " &
                        "LEFT JOIN VW_Descuentos d " &
                        "ON d.ART_Codigo = aa.ART_Codigo " &
                        "LEFT JOIN Articulo a " &
                        "ON a.ART_Codigo = aa.ART_Codigo AND a.ART_Activo = '1' " &
                        "INNER JOIN UnidadMedida um " &
                        "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " &
                        "INNER JOIN ArticuloProveedor ap " &
                        "ON ap.ART_Codigo = a.ART_Codigo AND ap.ARP_Activo = '1' " &
                        "INNER JOIN Proveedor p " &
                        "ON p.PROV_Codigo = ap.PROV_Codigo AND p.PROV_Activo = '1' " &
                        "WHERE aa.ARA_Activo = '1' AND LEN(aa.ART_Codigo) > 6 AND aa.ALM_Codigo = '00' " &
                        "AND aa.ARA_Stock > 0 AND aa.ART_Codigo IN (SELECT ART_Codigo FROM DescuentoArticulo WHERE DAR_Activo = '1'"

                If aPartirde <> 0 And dscto <> 0 Then
                    query = query & " AND DSAR_CantidadMin >= " & aPartirde & "AND DSAR_Porcentaje * 100 = " & dscto & ") "
                ElseIf aPartirde <> 0 And dscto = 0 Then
                    query = query & " AND DSAR_CantidadMin >= " & aPartirde & ") "
                ElseIf aPartirde = 0 And dscto <> 0 Then
                    query = query & " AND DSAR_Porcentaje * 100 = " & dscto & ") "
                Else
                    query = query & ") "
                End If
            End If

            If codArticulo <> "" Then
                query = query & " AND aa.ART_Codigo = '" & codArticulo & "' "
            End If
            If descrip <> "" Then
                query = query & " AND a.ART_Descripcion LIKE '%" & descrip & "%' "
            End If
            If codProveedor <> "" Then
                query = query & " AND ap.PROV_Codigo = '" & codProveedor & "'"
            End If
            If codFamilia <> "" Then
                query = query & " AND a.FAM_Codigo LIKE '" & codFamilia & "%'"
            End If

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerDescuentosXArticulo(codArticulo As String, tipo As Integer) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Dim query As String = ""
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            If tipo = 1 Then
                query = "SELECT CAST(DSAR_CantidadMin AS INT) AS Cantidad, " &
                        "CAST(CAST(DSAR_Porcentaje * 100 AS Decimal(10,1)) AS VARCHAR(10)) + ' %' AS Porcentaje, " &
                        "CAST(DSAR_Porcentaje * 100 AS Decimal(10,1)) AS Porcen " &
                        "FROM DescuentoArticulo " &
                        "WHERE ART_Codigo = '" & codArticulo & "' " &
                        "AND DAR_Activo = '1' " &
                        "AND GETDATE() BETWEEN DAR_FechaInicio AND DAR_FechaFin " &
                        "GROUP BY CAST(DSAR_CantidadMin AS INT),CAST(CAST(DSAR_Porcentaje * 100 AS Decimal(10,1)) AS VARCHAR(10)) + ' %'," &
                        "CAST(DSAR_Porcentaje * 100 AS Decimal(10,1))"
            ElseIf tipo = 2 Then
                query = "SELECT DSAR_CantidadMin AS Cantidad, CAST((DSAR_Porcentaje * 100) AS DECIMAL(10,2)) AS Porcentaje, " &
                        "(CASE WHEN GETDATE() > DAR_FechaInicio AND GETDATE() < DAR_FechaFin THEN 0 " &
                        "WHEN DAR_FechaInicio > GETDATE() THEN 1 ELSE 2 END) AS Rango, " &
                        "CONVERT(VARCHAR(20),DAR_FechaInicio) AS FI, CONVERT(VARCHAR(20),DAR_FechaFin) AS FF, " &
                        "CONVERT(VARCHAR(20),DAR_FechaInicio,103) AS FIN " &
                        "FROM DescuentoArticulo " &
                        "WHERE ART_Codigo = '" & codArticulo & "' AND DAR_Activo = '1' AND DAR_FechaFin > DAR_FechaInicio " &
                        "ORDER BY (CASE WHEN GETDATE() >= DAR_FechaInicio AND " &
                        "GETDATE() <= DAR_FechaFin THEN 0 " &
                        "WHEN DAR_FechaInicio > GETDATE() THEN 1 ELSE 2 END), DSAR_CantidadMin, DAR_FechaFin DESC"
            End If

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Sub registrarDescuentos(cia As String, suc As String, artCodigo As String, cant As Integer, porcen As Decimal, fechaIni As String, fechaFin As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "USP_RegistrarDescuentos"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .CommandType = CommandType.StoredProcedure
            .Parameters.AddWithValue("@CIA", cia)
            .Parameters.AddWithValue("@SUC", suc)
            .Parameters.AddWithValue("@ART", artCodigo)
            .Parameters.AddWithValue("@CATMIN", cant)
            .Parameters.AddWithValue("@PORCE", porcen / 100)
            .Parameters.AddWithValue("@FINI", fechaIni)
            .Parameters.AddWithValue("@FFIN", fechaFin & " 23:59:59")
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub activarDescuentos(cia As String, suc As String, artCodigo As String, cantidad As Integer)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "UPDATE DescuentoArticulo " &
                              "SET DAR_FechaFin = GETDATE() " &
                              "WHERE CIA = @CIA AND SUC_Codigo = @SUC " &
                              "AND ART_Codigo = @ART AND DSAR_CantidadMin = @CANT " &
                              "AND DAR_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@CIA", cia)
            .Parameters.AddWithValue("@SUC", suc)
            .Parameters.AddWithValue("@ART", artCodigo)
            .Parameters.AddWithValue("@CANT", cantidad)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

End Class